package com.rickpan.repository;

import com.rickpan.entity.Project;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 项目数据访问层
 * 
 * @author RickPan Team
 * @since 2024-01-15
 */
@Repository
public interface ProjectRepository extends JpaRepository<Project, Long> {

    /**
     * 根据团队ID查找项目列表
     */
    List<Project> findByTeamIdOrderByCreatedAtDesc(Long teamId);

    /**
     * 根据团队ID和状态查找项目列表
     */
    List<Project> findByTeamIdAndStatusOrderByCreatedAtDesc(Long teamId, Project.Status status);

    /**
     * 根据团队ID查找项目列表（分页）
     */
    Page<Project> findByTeamIdOrderByCreatedAtDesc(Long teamId, Pageable pageable);

    /**
     * 根据团队ID和状态查找项目列表（分页）
     */
    Page<Project> findByTeamIdAndStatusOrderByCreatedAtDesc(Long teamId, Project.Status status, Pageable pageable);

    /**
     * 根据项目经理ID查找项目列表
     */
    List<Project> findByProjectManagerIdOrderByCreatedAtDesc(Long projectManagerId);

    /**
     * 根据项目经理ID和状态查找项目列表
     */
    List<Project> findByProjectManagerIdAndStatusOrderByCreatedAtDesc(Long projectManagerId, Project.Status status);

    /**
     * 检查团队中是否存在同名项目
     */
    boolean existsByTeamIdAndName(Long teamId, String name);

    /**
     * 检查团队中是否存在同名项目（排除指定项目ID）
     */
    @Query("SELECT CASE WHEN COUNT(p) > 0 THEN true ELSE false END FROM Project p " +
           "WHERE p.teamId = :teamId AND p.name = :name AND p.id != :excludeId")
    boolean existsByTeamIdAndNameExcludingId(@Param("teamId") Long teamId, 
                                           @Param("name") String name, 
                                           @Param("excludeId") Long excludeId);

    /**
     * 根据团队ID统计项目数量
     */
    long countByTeamId(Long teamId);

    /**
     * 根据团队ID和状态统计项目数量
     */
    long countByTeamIdAndStatus(Long teamId, Project.Status status);

    /**
     * 查找活跃项目
     */
    @Query("SELECT p FROM Project p WHERE p.status = 'ACTIVE' ORDER BY p.updatedAt DESC")
    List<Project> findActiveProjects(Pageable pageable);

    /**
     * 查找即将到期的项目
     */
    @Query("SELECT p FROM Project p WHERE p.endDate BETWEEN :startDate AND :endDate " +
           "AND p.status IN ('PLANNING', 'ACTIVE') ORDER BY p.endDate ASC")
    List<Project> findProjectsDueSoon(@Param("startDate") LocalDateTime startDate, 
                                     @Param("endDate") LocalDateTime endDate);

    /**
     * 查找逾期项目
     */
    @Query("SELECT p FROM Project p WHERE p.endDate < :currentDate " +
           "AND p.status IN ('PLANNING', 'ACTIVE') ORDER BY p.endDate ASC")
    List<Project> findOverdueProjects(@Param("currentDate") LocalDateTime currentDate);

    /**
     * 根据团队ID搜索项目
     */
    @Query("SELECT p FROM Project p WHERE p.teamId = :teamId " +
           "AND (p.name LIKE %:keyword% OR p.description LIKE %:keyword%) " +
           "ORDER BY p.updatedAt DESC")
    List<Project> searchProjectsByTeam(@Param("teamId") Long teamId, 
                                      @Param("keyword") String keyword);

    /**
     * 根据团队ID搜索项目（分页）
     */
    @Query("SELECT p FROM Project p WHERE p.teamId = :teamId " +
           "AND (p.name LIKE %:keyword% OR p.description LIKE %:keyword%) " +
           "ORDER BY p.updatedAt DESC")
    Page<Project> searchProjectsByTeam(@Param("teamId") Long teamId, 
                                      @Param("keyword") String keyword, 
                                      Pageable pageable);

    /**
     * 根据优先级查找项目
     */
    List<Project> findByTeamIdAndPriorityOrderByCreatedAtDesc(Long teamId, Project.Priority priority);

    /**
     * 查找最近更新的项目
     */
    @Query("SELECT p FROM Project p WHERE p.teamId = :teamId ORDER BY p.updatedAt DESC")
    List<Project> findRecentProjectsByTeam(@Param("teamId") Long teamId, Pageable pageable);

    /**
     * 根据进度范围查找项目
     */
    @Query("SELECT p FROM Project p WHERE p.teamId = :teamId " +
           "AND p.progress BETWEEN :minProgress AND :maxProgress " +
           "ORDER BY p.progress DESC")
    List<Project> findProjectsByProgressRange(@Param("teamId") Long teamId, 
                                             @Param("minProgress") Integer minProgress, 
                                             @Param("maxProgress") Integer maxProgress);

    /**
     * 获取团队项目统计信息
     */
    @Query("SELECT p.status as status, COUNT(p) as count FROM Project p " +
           "WHERE p.teamId = :teamId GROUP BY p.status")
    List<ProjectStatusCount> getProjectStatusStatsByTeam(@Param("teamId") Long teamId);

    /**
     * 项目状态统计接口
     */
    interface ProjectStatusCount {
        Project.Status getStatus();
        Long getCount();
    }
}
